package com.meow.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class XLSExportor {
	
	private static Logger logger = Logger.getLogger(XLSExportor.class);
	
	/**
	 * 导出数据<br>
	 * 该方法用于导出大数据量
	 * @param request
	 * @param response
	 * @param conn 数据库连接
	 * @param sql 需要执行的SQL语句
	 * @param title 标题
	 * @param limit 导出的表头以及数据列信息
	 */
	public static void doExport(HttpServletRequest request, HttpServletResponse response, Connection conn, String sql, String title, ExtLimit limit) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(title);
			
			String[] exp_column_names = limit.getExp_column_names().split(",");
			String[] exp_column_indexs = limit.getExp_column_indexs().split(",");
			
			HSSFCellStyle style = wb.createCellStyle();
			style.setFillForegroundColor(HSSFColor.LIME.index);
		    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			//生成表头
			HSSFRow row = sheet.createRow(0);
			for(int j = 0; j < exp_column_names.length; j++) {
				HSSFCell cell = row.createCell((short)j);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellStyle(style);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(exp_column_names[j]);
			}
			
			Statement stmt = null;
			ResultSet rs = null;
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			int i = 1;
			while(rs.next()) {
				row = sheet.createRow(i++);
				
				for(int j = 0; j < exp_column_indexs.length; j++) {
					Object cellValue = null;
					
					try {
						cellValue = rs.getObject(exp_column_indexs[j]);
					}
					catch (Exception e) {
					}
					
					if(cellValue == null)
						continue;
					
					HSSFCell cell = row.createCell((short)j);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					if(cellValue == null) {
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue("");
					}
					else {
						if(cellValue instanceof java.util.Date) {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(cellValue));
						}
						else {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(cellValue.toString());
						}
					}
				}
			}
			
			rs.close();
			stmt.close();
			
			response.setHeader("Content-Disposition", "attachment;filename=\"" + limit.getExp_name() + "\"");
	        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
	        response.setHeader("Pragma", "public");
	        response.setDateHeader("Expires", (System.currentTimeMillis() + 1000));
	        response.setContentType("application/vnd.ms-excel" + ";charset=UTF-8");
	        wb.write(response.getOutputStream());
	        response.getOutputStream().flush();
	        response.getOutputStream().close();
		}
		catch (Exception e) {
			logger.error("导出数据错误！", e);
		}
		finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 导出数据<br>
	 * 该方法适用于小数据量导出，如果数据量超过2000条，不宜使用，否则会造成内存溢出
	 * @param request
	 * @param response
	 * @param dataList 导出的数据列表
	 * @param title 标题
	 * @param limit 导出的表头以及数据列信息
	 */
	public static void doExport(HttpServletRequest request, HttpServletResponse response, List dataList, String title, ExtLimit limit) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(title);
			
			String[] exp_column_names = limit.getExp_column_names().split(",");
			String[] exp_column_indexs = limit.getExp_column_indexs().split(",");
			
			HSSFCellStyle style = wb.createCellStyle();
			style.setFillForegroundColor(HSSFColor.LIME.index);
		    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			//生成表头
			HSSFRow row = sheet.createRow(0);
			for(int j = 0; j < exp_column_names.length; j++) {
				HSSFCell cell = row.createCell((short)j);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellStyle(style);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(exp_column_names[j]);
			}
			
			//生成数据
			for(int i = 0; i < dataList.size(); i++) {
				row = sheet.createRow(i + 1);
				Object dataObj = dataList.get(i);
				
				for(int j = 0; j < exp_column_indexs.length; j++) {
					Object cellValue = EntityReflect.getObjectProperty(dataObj, exp_column_indexs[j]);
					HSSFCell cell = row.createCell((short)j);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					if(cellValue == null) {
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue("");
					}
					else {
						if(cellValue instanceof java.util.Date) {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(cellValue));
						}
						else {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(cellValue.toString());
						}
					}
				}
			}
			
			response.setHeader("Content-Disposition", "attachment;filename=\"" + limit.getExp_name() + "\"");
	        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
	        response.setHeader("Pragma", "public");
	        response.setDateHeader("Expires", (System.currentTimeMillis() + 1000));
	        response.setContentType("application/vnd.ms-excel" + ";charset=UTF-8");
	        wb.write(response.getOutputStream());
	        response.getOutputStream().flush();
	        response.getOutputStream().close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
}
